package dao.impl;

import dao.StudentDao;
import model.Student;
import util.DBUtils;
import util.StudentEnum;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class StudentDaoImpl extends DBUtils implements StudentDao {

    @Override
    public int addStudent(Student student) {
        int i = -1;
        try {
            List params = new ArrayList();
            params.add(student.getStuName());
            params.add(student.getStuNo());
            params.add(student.getStuSex());
            params.add(student.getStuPhone());
            params.add(student.getStuEmail());
            params.add(student.getStuRegistered());
            params.add(student.getStuAddress());
            params.add(student.getStuProfession());
            params.add(student.getStuIdnumber());
            params.add(student.getStuPolitice());
            params.add(new Date());
            params.add(StudentEnum.READING.type);//1表示在读
            params.add(student.getStuIntroduction());
            params.add(student.getGid());
            String sql = "insert into student(stuname,stuno,stusex,stuphone,stuemail,sturegistered,stuaddress,stuprofession,stuidnumber,stupolitice,sturegdate,stustats,stuintroduction,gid) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
            i = update(sql, params);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public List<Student> getAll(String stuname, String stuno, int stusex, int pageIndex, int pageSize) {
        List params = new ArrayList();
        StringBuffer sql = new StringBuffer("select * from student where stustats!=4");
        if (stuname != null && stuname.length() > 0) {
            sql.append(" and stuname like ? ");
            params.add("%" + stuname + "%");
        }
        if (stuno != null && stuno.length() > 0) {
            sql.append(" and stuno = ? ");
            params.add(stuno);
        }
        if (stusex != -1) {
            sql.append(" and stusex = ? ");
            params.add(stusex);
        }

        sql.append(" limit ?,?");
        //limit((pageIndex-1)*pageSize，pageSize)
        params.add((pageIndex - 1) * pageSize);
        params.add(pageSize);

        resultSet = super.query(sql.toString(), params);
        List<Student> studentList = new ArrayList<>();
        Student student = null;
        if (resultSet != null) {
            try {
                while (resultSet.next()) {
                    student = new Student();
                    student.setStuId(resultSet.getInt("stuid"));
                    student.setStuName(resultSet.getString("stuname"));
                    student.setStuNo(resultSet.getString("stuno"));
                    student.setStuSex(Integer.parseInt(resultSet.getString("stusex")));
                    student.setStuPhone(resultSet.getString("stuphone"));
                    student.setStuEmail(resultSet.getString("stuemail"));
                    student.setStuRegistered(resultSet.getString("sturegistered"));
                    student.setStuAddress(resultSet.getString("stuaddress"));
                    student.setStuProfession(resultSet.getString("stuprofession"));
                    student.setStuIdnumber(resultSet.getString("stuidnumber"));
                    student.setStuPolitice(resultSet.getString("stupolitice"));
                    student.setStuRegdate(resultSet.getDate("sturegdate"));
                    student.setStuStats(resultSet.getInt("stustats"));
                    student.setStuIntroduction(resultSet.getString("stuintroduction"));
                    student.setGid(resultSet.getInt("gid"));
                    studentList.add(student);
                }
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            } finally {
                closeAll();
            }
        }
        return studentList;
    }

    @Override
    public int total(String stuname, String stuno, int stusex) {
        List params = new ArrayList();
        int total = 0;
        StringBuffer sql = new StringBuffer("select COUNT(*) from student where stustats!=4 ");
        if (stuname != null && stuname.length() > 0) {
            sql.append(" and stuname like ? ");
            params.add("%" + stuname + "%");
        }
        if (stuno != null && stuno.length() > 0) {
            sql.append(" and stuno = ? ");
            params.add(stuno);
        }
        if (stusex != -1) {
            sql.append(" and stusex = ? ");
            params.add(stusex);
        }
        resultSet = query(sql.toString(), params);
        try {
            while (resultSet.next()) {
                total = resultSet.getInt(1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return total;
    }

    @Override
    public Student findById(int sid) {
        String sql = "select * from student where stuid=? and stustats!=4";
        resultSet = query(sql, sid);
        Student student = new Student();
        try {
            while (resultSet.next()) {
                student.setStuId(resultSet.getInt("stuid"));
                student.setStuName(resultSet.getString("stuname"));
                student.setStuNo(resultSet.getString("stuno"));
                student.setStuSex(Integer.parseInt(resultSet.getString("stusex")));
                student.setStuPhone(resultSet.getString("stuphone"));
                student.setStuEmail(resultSet.getString("stuemail"));
                student.setStuRegistered(resultSet.getString("sturegistered"));
                student.setStuAddress(resultSet.getString("stuaddress"));
                student.setStuProfession(resultSet.getString("stuprofession"));
                student.setStuIdnumber(resultSet.getString("stuidnumber"));
                student.setStuPolitice(resultSet.getString("stupolitice"));
                student.setStuRegdate(resultSet.getDate("sturegdate"));
                student.setStuStats(resultSet.getInt("stustats"));
                student.setStuIntroduction(resultSet.getString("stuintroduction"));
                student.setGid(resultSet.getInt("gid"));
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            closeAll();
        }
        return student;
    }

    @Override
    public int updateStudent(Student student) {
        String sql = "update student set stuname=?,stusex=?,stuphone=?,stuemail=?,sturegistered=?,stuaddress=?,stuprofession=?,stupolitice=?,stuintroduction=?,gid=? where stuid=?";
        int update = -1;
        try {
            update = update(sql,
                    student.getStuName(),
                    student.getStuSex(),
                    student.getStuPhone(),
                    student.getStuEmail(),
                    student.getStuRegistered(),
                    student.getStuAddress(),
                    student.getStuProfession(),
                    student.getStuPolitice(),
                    student.getStuIntroduction(),
                    student.getGid(),
                    student.getStuId());
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return update;
    }

    @Override
    public int deleteById(int id) {
        String sql = "delete from student where stuid=?";
        int i = -1;
        try {
            i = update(sql, id);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }

    @Override
    public int deleteStats(int sid) {
        String sql = "update student set stustats=? where stuid=?";
        int i = -1;
        try {
            i = update(sql, StudentEnum.DELETE.type, sid);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            closeAll();
        }
        return i;
    }
}
